DECLARE @strXML AS XML
DECLARE @rootnode NVARCHAR(255)
     
SET @strXML = '<items>
            <item id="0001" type="Donut">
                <name>Cake</name>
                <ppu>0.55</ppu>
                    <batter id="1001">Regular</batter>
                    <batter id="1002">Chocolate</batter>
                    <batter id="1003">Blueberry</batter>
                <topping id="5001">None</topping>
                <topping id="5002">Glazed</topping>
                <topping id="5005">Sugar</topping>
                <topping id="5006">Sprinkles</topping>
                <topping id="5003">Chocolate</topping>
                <topping id="5004">Maple</topping>
            </item>
        </items>'
SET @rootnode = 'items'

SET NOCOUNT ON

DECLARE
    @strText AS NVARCHAR(MAX)
   ,@idoc INT
   ,@id INT
   ,@parentid INT

IF OBJECT_ID('tempdb..#ChildList') IS NOT NULL
  DROP TABLE #ChildList

CREATE TABLE #ChildList (
    [RowNum] INT IDENTITY(1,1) NOT NULL,
    [parentid] INT NULL,
    [id] INT NULL,
    PRIMARY KEY (RowNum))
    

IF OBJECT_ID('tempdb..#NodeList') IS NOT NULL
DROP TABLE #NodeList

CREATE TABLE #NodeList (
    [RowNum] INT NOT NULL,
    [id] INT NULL,
    [parentid] INT NULL,
    [nodetype] INT NULL,
    [localname] NVARCHAR(MAX) NULL,
    [text] NVARCHAR(MAX) NULL,
    PRIMARY KEY (RowNum))

SET @id = 1
SET @parentid = NULL


/* Get rid of tabs and extra spaces */

SET @strText = CAST(@strXML AS NVARCHAR(MAX))

SET @strText = 
REPLACE(
    REPLACE(
        REPLACE(
            REPLACE(
                @strText
            ,'  ',' '+CHAR(7))
        ,CHAR(7)+' ','')
    ,CHAR(7),'')
,CHAR(9),'    ')
    
SET @strXML = CONVERT(XML, @strText)

/* Validate the XML */

EXEC sp_xml_preparedocument @idoc OUTPUT, @strXML

/* Parse the XML data */

;WITH cteChildren (parentid, id) AS 
(
    SELECT 
         CAST(p1.parentid AS INT) AS parentid
        ,CAST(p1.id AS INT) AS id
    FROM OPENXML (@idoc,@rootnode,2) AS p1
    UNION ALL
    SELECT 
         CAST(p2.parentid AS INT) AS parentid
        ,CAST(p2.id AS INT) AS id
    FROM OPENXML (@idoc,@rootnode,2) AS p2
    INNER JOIN cteChildren AS cte ON (cte.id = p2.ParentID)
    WHERE
        p2.parentid = @parentid         
)
INSERT INTO #ChildList
SELECT 
    parentid
   ,id 
FROM cteChildren
    
        
INSERT INTO #NodeList
SELECT 
     #ChildList.RowNum
    ,xmllist.id 
    ,xmllist.parentid
    ,xmllist.nodetype
    ,xmllist.localname
    ,CAST(xmllist.[text] AS NVARCHAR(MAX)) AS [text]
FROM #ChildList
INNER JOIN OPENXML (@idoc,@rootnode,2) AS xmllist ON (#ChildList.id = xmllist.id)
WHERE
    #ChildList.RowNum > 0
    
/* Display the results */

IF OBJECT_ID('dbo.XML_Nodes') IS NOT NULL
  DROP TABLE dbo.XML_Nodes
                
;WITH RecursiveNodes(RowNum,id,parentid,nodepath,localname,[text],nodetype) AS 
(
    SELECT
         #NodeList.RowNum
        ,#NodeList.id
        ,#NodeList.parentid
        ,CAST('/' + REPLACE(REPLACE(REPLACE(REPLACE(#NodeList.localname,'&',''),'?',''),' ',''),'.','') AS NVARCHAR(255)) AS nodepath
        ,#NodeList.localname
        ,CAST(#NodeList.[text] AS NVARCHAR(MAX)) AS [text]
        ,0 AS nodetype
    FROM #ChildList
    INNER JOIN #NodeList ON (#ChildList.id = #NodeList.id)
    WHERE
        #NodeList.parentid IS NULL
        AND #ChildList.RowNum > 0
        AND #NodeList.RowNum > 0
        
    UNION ALL

    SELECT
         n.RowNum
        ,n.id
        ,n.parentid
        ,CAST(r.nodepath + '/'+ REPLACE(REPLACE(REPLACE(REPLACE(n.localname,'&',''),'?',''),' ',''),'.','') AS NVARCHAR(255)) AS nodepath
        ,n.localname
        ,n.[text]
        ,n.nodetype
    FROM #NodeList AS n
    INNER JOIN RecursiveNodes AS r ON (n.parentid = r.id)
    WHERE
        n.RowNum > 0
        AND r.RowNum > 0
        AND n.parentid >= 0
)
SELECT
    ROW_NUMBER() OVER (ORDER BY Result.RowNum) AS RowNum
   ,Result.id
   ,Result.parentid
   ,Result.nodepath
   ,Result.nodetype
   ,Result.nodename
   ,Result.property
   ,Result.value
   ,Result.nodecontents
INTO dbo.XML_Nodes
FROM
    (
    SELECT
        rn.RowNum
       ,rn.id
       ,rn.parentid
       ,rn.nodepath
       ,(CASE
            WHEN rn.nodetype = 0 THEN 'Root'
            WHEN rn.nodetype = 1 THEN 'Element'
            WHEN rn.nodetype = 2 THEN 'Attribute'
            ELSE 'Data'
         END) AS nodetype
       ,(CASE
            WHEN rn.nodetype = 0 THEN rn.localname
            WHEN rn.nodetype = 1 THEN rn.localname
            WHEN rn.nodetype = 2 THEN (SELECT TOP(1) localname FROM RecursiveNodes WHERE id = rn.parentid)
            ELSE NULL
         END) AS nodename
       ,(CASE
            WHEN rn.nodetype = 2 THEN rn.localname
            ELSE NULL
         END) AS property
       ,(CASE
            WHEN rn.nodetype = 2 THEN (SELECT TOP(1) [text] FROM RecursiveNodes WHERE parentid = rn.id)
            ELSE NULL
         END) AS value         
       ,(CASE
            WHEN rn.nodetype = 1 THEN (SELECT TOP(1) [text] FROM RecursiveNodes WHERE parentid = rn.id)
            WHEN rn.nodetype = 2 THEN (SELECT TOP(1) [text] FROM RecursiveNodes WHERE parentid = rn.parentid and [text] is not null)
            ELSE NULL
         END) AS nodecontents    
    FROM RecursiveNodes AS rn
    WHERE
        rn.localname <> '#text'
    ) AS Result
WHERE
    Result.id >= 0
    AND (Result.id = 0
        OR property IS NOT NULL
        OR value IS NOT NULL
        OR nodecontents IS NOT NULL)
 
        
	SELECT
		RowNum
	   ,id
	   ,parentid
	   ,nodepath
	   ,nodetype
	   ,nodename
	   ,property
	   ,value
	   ,nodecontents
	   ,CASE 
			WHEN nodetype = 'Attribute' THEN 
				REPLACE(nodepath,'/'+ property,'[@' + property + '="' + value + '"]')
			
			ELSE ''
		END AS [Singleton XPath]
	FROM
		dbo.XML_Nodes


EXEC sp_xml_removedocument @idoc
            